﻿using System;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Threading;
using ExcelRefresh.Com;
using Microsoft.Office.Interop.Excel;

namespace ExcelRefresh.Excel
{
    public abstract class ExcelUtilities : ComHelpers
    {
        public static bool IsFileLocked( FileInfo file)
        {
            try
            {
                using (var stream = file.OpenWrite())
                    return false;
            }
            catch (IOException)
            {
                return true;
            }
        }

        public static bool IsFileLocked(string path)
        {
            return IsFileLocked(new FileInfo(path));
        }

        public static void RefreshWorkbook(string sourcePath, string saveToPath, string backupPath)
        {
            if (IsFileLocked(sourcePath))
                throw new FileInUseException("Cannot open " + sourcePath + " for writing. File is in use.");

            var startTime = DateTime.Now;

            UsingWorkbook(sourcePath, 
                book =>
                {
                    if (TryRefreshPowerPivot(book))
                    {
                        Trace.TraceInformation("Refreshed PowerPivot {1} - Duration: {0}", DateTime.Now - startTime, book.Name);
                        book.Comments = string.Format("Refreshed PowerPivot : {1:dd MMM, HH\\:mm}  Duration: {0:mm\\:ss}", DateTime.Now - startTime, DateTime.Now);

                        if (sourcePath == saveToPath)
                            book.Save();
                        else
                            book.SaveCopyAs(saveToPath);

                        sourcePath = saveToPath;
                    }
                },
                book =>
                {
                    ProcessAllRefreshables(book, startTime);
                    Trace.TraceInformation("Refreshed {1}  - Duration: {0}", DateTime.Now - startTime, book.Name);
                    book.Comments = string.Format("Refreshed: {1:dd MMM, HH\\:mm}  Duration: {0:mm\\:ss}", DateTime.Now - startTime, DateTime.Now);
                    if (sourcePath == saveToPath)
                        book.Save();
                    else
                        book.SaveCopyAs(saveToPath);

                    if (!string.IsNullOrEmpty(backupPath))
                    {
                        var folder = Path.GetDirectoryName(backupPath);
                        if (!Directory.Exists(folder))
                            Directory.CreateDirectory(folder);

                        var statusProp = book.BuiltinDocumentProperties["Content Status"];
                        statusProp.Value = "Archive";
                        book.SaveCopyAs(backupPath);
                    }
                });
        }


        #region Powerpivot Refreshing

        public const int MaxParallelPowerPivotQueries = 3;

        private static string RefreshCubeCommand =
              "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" +
              "<Parallel MaxParallel=\"" + MaxParallelPowerPivotQueries.ToString() + "\">" +
              "<Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" +
              "<Object><DatabaseID>{0}</DatabaseID></Object>" +
              "<Type>{1}</Type>" +
              "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" +
              "</Process>" +
              "</Parallel>" +
              "</Batch>";


        /// <summary>
        /// Connects to and refreshes any PowerPivot cube embedded in an Excel workbook
        /// </summary>
        /// <param name="book"></param>
        public static bool TryRefreshPowerPivot(Workbook book)
        {
            if (book.ReadOnly)
                throw new FileInUseException("Excel file in use");

            WorkbookConnection powerPivotConnection;
            int retryCount = 0;

            Trace.TraceInformation("Checking '{0}' for any PowerPivot cube", book.Name);

            while (true)
            {
                try
                {
                    powerPivotConnection = book.Connections
                        .OfType<WorkbookConnection>()
                        .Where(n => n.Name == "PowerPivot Data" || n.Name == "ThisWorkbookDataModel")
                        .FirstOrDefault();

                    // Not a PowerPivot workbook? Then gracefully exit
                    if (powerPivotConnection == null)
                    {
                        Trace.TraceInformation("Workboook '{0}' does not contain a PowerPivot cube.", book.Name);
                        return false;
                    }

                    powerPivotConnection.OLEDBConnection.Reconnect();
                    break;
                }
                catch (Exception ex)
                {
                    if (++retryCount < 3)
                        Thread.Sleep(new TimeSpan(0, 0, retryCount * 60));
                    else
                        throw new Exception(string.Format("Error Connecting to PowerPivot in '{0}' : {1}", book.Name, ex.Message));
                }
            }

            Trace.TraceInformation("Refreshing PowerPivot: {0}", book.Name);
            try
            {
                UsingCOM((ADODB.Connection)powerPivotConnection.OLEDBConnection.ADOConnection, ExecuteProcessFull);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Error Executing Refresh Command Against PowerPivot in '{0}' : {1}", book.Name, ex.Message));

            }
        }

        /// <summary>
        /// Sends a ProcessFull DMX command to an Analysis Services cube via an ADO connection
        /// </summary>
        /// <param name="connection"></param>
        private static void ExecuteProcessFull(ADODB.Connection connection)
        {
            const string qry = "select distinct object_id from $system.discover_object_activity where object_parent_path = 'Global.Sessions.SPID_{0}'";

            var spids = connection.RunQuery(
                "select session_spid from $system.discover_sessions",
                fields => string.Format(qry, fields[0].Value))
                .ToArray();

            var parts = spids
                .SelectMany(c => connection.RunQuery(c, fields => fields[0].Value.ToString().Split('.')))
                .FirstOrDefault(c => c.Length > 3 && c[0] == "Permissions" && c[2] == "Databases");

            if (parts == null)
                throw new Exception("Cannot retrieve database ID from PowerPivot cube");

            var databaseId = parts[3];
            object recordsAffected;

            var query = string.Format(RefreshCubeCommand, databaseId, "ProcessFull");
            connection.CommandTimeout = 40000;
            connection.Execute(query, out recordsAffected);

            Thread.Sleep(500);      // A quick snooze seems to help Excel Automation threads to catch up.
        }

        #endregion

        #region Excel Data Refreshing

        public static RefreshableProxy[] GetRefreshableProxies( Workbook workbook)
        {
            var connections = workbook.Connections.Cast<WorkbookConnection>().ToArray();

            var OleDbRefreshes = from c in connections
                                 where c.Type == XlConnectionType.xlConnectionTypeOLEDB
                                 let oc = c.OLEDBConnection
                                 where oc.EnableRefresh
                                 select new RefreshableProxy(oc, c.Name);

            var OdbcRefreshes = from c in connections
                                where c.Type == XlConnectionType.xlConnectionTypeODBC
                                let oc = c.ODBCConnection
                                where oc.EnableRefresh
                                select new RefreshableProxy(oc, c.Name);

            var queryTables = from Worksheet sheet in workbook.Worksheets
                              from QueryTable queryTable in sheet.QueryTables
                              where queryTable.EnableRefresh
                                 && queryTable.QueryType != XlQueryType.xlOLEDBQuery
                                 && queryTable.QueryType != XlQueryType.xlODBCQuery
                              select new RefreshableProxy(queryTable, queryTable.Name);

            //var pivotCaches = from PivotCache cache in (PivotCaches)workbook.PivotCaches()
            //                  where cache.EnableRefresh
            //                  select new RefreshableProxy(cache,
            //                      string.Format("{0} - Pivot Cache {1}", workbook.FullName, cache.Index)
            //                      );

            var pivotTables = from Worksheet sheet in workbook.Worksheets
                              from PivotTable pivotTable in (PivotTables)sheet.PivotTables()
                              where !pivotTable.ManualUpdate
                              select new RefreshableProxy(pivotTable, pivotTable.Name + " on " + sheet.Name);

            return OdbcRefreshes
                .Concat(OleDbRefreshes)
                .Concat(queryTables)
                //.Concat(pivotCaches)
                .Concat(pivotTables)
                .ToArray();
        }

        public static void ProcessAllRefreshables(Workbook workbook, DateTime startTime)
        {
            var refreshables = GetRefreshableProxies(workbook);

            foreach (var refreshable in refreshables)
            {
                try
                {
                    var st = DateTime.Now;
                    refreshable.Refresh();
                    Trace.TraceInformation("Refreshed {0} on {1}. Duration: {2:mm\\:ss}", refreshable.Name, workbook.FullName, (DateTime.Now - st));
                    while (refreshable.IsRefreshing)
                        Thread.Sleep(250);
                }
                catch (Exception ex)
                {
                    throw new Exception(refreshable.Name + ": " + ex.Message);
                }
            }
        }

        #endregion
    }
}
